Přeskočit na hlavní obsah

Otázka č. 23 - Typy DBMS, zamykání tabulek, DCL a TCL SQL

Typy DBMS

  • DBSM (Database Management System) je systém, který umožňuje uživatelům vytvářet, udržovat a manipulovat s databázemi.

1. RDBMS (Relational Database Management System)​

  • Založen na relačním modelu dat

  • Ukládání dat do tabulek

  • Vztahy vyjádřeny klíči

  • Pro manipulaci slouží SQL

  • Př: MySQL, Oracle, Microsoft SQL Server

2. OODBMS (Object-Oriented Database Management System)​

  • Ukládání dat ve formě objektů

  • Objekty mohou obsahovat metody a atributy

  • Vhodný pro aplikace s komplexními datovými strukturami

  • Př: ObjectDB, db4o

3. ORDBMS (Object-Relational Database Management System)​

  • Kombinuje vlastnosti relačních a objektově orientovaných DB
  • umožňnuje ukládat strukturovaná data jako v relačních DB, ale také zahrnuje objektové funkce a datové typy
  • Př: PostgreSQL, Oracle

4. NoSQL​

  • Navrženy pro ukládání, zpracování a získávání dat, která nejsou dobře modelovatelná v rámci relačního schématu

Př: MongoDB, Cassandra, Redis

5. GraphDB​

  • Navržená pro práci s daty reprezentovanými ve formě grafů
  • Matimatické struktury skládájicí se z uzlů spojenými hranami, které mohou reprezentovat různé vztahy mezi daty

Př: Neo4j, Microsoft Azure Cosmos DB

6. TSDB​

  • Optimalizovaný pro ukládání a dotazování nad časově řadovými daty

  • Data jsou uspořádána podle časového razítka

  • Telemetrická data, výkazy měření, záznamy senzorů

  • Př: InfluxDB, Prometheus

Zamykání tabulek

  • Mechanismus, který zajišťuje, že operace s databází, jako jsou čtení a zápis, aktualizace a odstranění, jsou prováděny atomicky, konzistentně a trvale
  • Zamykání tabulek může být provedeno pomocí příkazu LOCK TABLE
  • Režimy zamykání: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
  • Uvolnění zámku z tabulky - UNLOCK TABLE
  • WAIT - pokud je zámek zamčen, čeká na jeho uvolnění
  • NOWAIT - pokud je zámek zamčen, vrátí chybu
-- Zamknutí tabulky
LOCK TABLE my_table IN EXCLUSIVE MODE;

-- Provedení operace nad zamčenou tabulkou
SELECT * FROM my_table;
UPDATE my_table SET column1 = value1 WHERE condition;
DELETE FROM my_table WHERE condition;

-- Odemknutí tabulky
UNLOCK TABLE my_table;

Bezpečnost

User​

  • vytvoření uživatele
-- vyvtvoření uživatele
CREATE USER 'NázevUčtu'@‘Síť, ze které se může připojit' IDENTIFIED BY 'heslo‘;

CREATE USER 'Vojta'@'localhost' IDENTIFIED BY 'tajneheslo123'
  • Vytvoření uživatele s použitím autentizačního pluginu pro zahashovaní hesla, heslo po 180 dnech vyprší, má 3 pokusy na přihlášení, jinak se učet zablokuje na 2 dny
CREATE USER 'Vojta'@'localhost' IDENTIFIED WITH catching_sha2_password BY 'tajneheslo' PASSWORD EXPIRE INTERVAL 180 DAY FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;
  • Vytvoření uživatele, který potřebuje platný ssl certifikát
CREATE USER 'Vojta'@'localhost' REQUIRE SSL;
  • Vytvoření uživatele s omezením na DDL dotazy
CREATE USER 'Vojta'@'localhost' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 100;
  • Vytvoření uživatele, kterému je zakázáno používat 6 nejnovějších hesel, které měl
CREATE USER 'Vojta'@'localhost' PASSWORD HISTORY 6;
  • Odstranění uživalete
DROP USER 'Vojta'@'localhost';
  • Změna uživatele
RENAME USER 'Vojta'@'localhost' TO 'Vojta2'@'localhost';

Salt​

  • Unikátní a náhodný řetězec, který nemusí být tajný a může být uložen vedle hashu hesla v databázi

Pepper​

  • Aplikováno před hashováním
  • Uložen odděleně od databáze, např. ve formě konfiguračního souboru

Hashovací funkce​

  • o Pomocí algoritmu vytvoří zahashované heslo pevné délky, které nelze převést na původní heslo, SHA2 (Sha-512, Sha-384, Sha-256)

DCL SQL

  • Sada příkazů, které se týkají práv, oprávnění a dalších kontrol v rámci databázového systému

GRANT​

  • Slouží k udělení oprávnění uživatelům nebo rolím, povolit přístup k určitým tabulkám, pohledům, uloženým procedurám a funkcím
GRANT ALL ON my_table TO 'Vojta'@'localhost';
GRANT 'role' TO 'Vojta'@'localhost';
GRANT SELECT, INSERT, UPDATE ON my_table TO 'role';

REVOKE​

  • Slouží k odebrání oprávnění, které byla udělena pomocí GRANT, omezí přístup uživatelů k určitým tabulkám, pohledům, uloženým procedurám a funkcím
REVOKE ALL ON my_table FROM 'Vojta'@'localhost';
REVOKE 'role' FROM 'Vojta'@'localhost';
REVOKE SELECT, INSERT, UPDATE ON my_table FROM 'role';

DENY​

  • o Slouží k explicitnímu zakázaní přístupu k objektům v databázi, uživatelé nebo role nebudou mít oprávnění i když jim byla udělena pomocí GRANT

ROLE​

  • Skupina oprávnění, které mohou být uděleny uživatelům, výhoda nemusí se nastavovat každý uživatel a stačí jemu přidělá role
CREATE ROLE 'role';
CREATE ROLE 'webapp'@'localhost';

transakce, savepointy a pravidlo ACID​

TRANSAKCE​

  • Skupina příkazů, které buď proběhnou úspěšně celé, nebo se žádná z nich neuskuteční
  • Typické operace v rámci transakce zahrnují vkládání, aktualizaci nebo mazání dat.
  • Transakce musí být atomické, konzistentní, izolované a trvalé, což odpovídá pravidlu ACID.

ACID​

  • A - Atomicity (Atomičnost)
    • Transakce musí být atomické, buď proběhnou všechny příkazy nebo žádný
  • C - Consistency (Konzistence)
    • Musí zajistit, že data budou před provedení transakce v konzistentním stavu. To znamená, že transakce musí respektovat všechna pravidla integrity dat
  • I - Isolation (Izolace)
    • Neměla by ovlivňovat provádění jiných transakcí. Měly by pracovat nezávisle na ostatních
  • D - Durability (Trvalost)
    • Po úspěšném dokončení transakce musí být změny v databázi trvalé a odolné vůči poruchám systému

SAVEPOINT​

  • Bod v rámci transakce, na které lze nahlížet jako na mezi stav
  • Umožňují provádět části transakce nezávisle na celku
  • Ukládání stavu transakce v průběhu provádění, aby bylo možné provést částečné zrušení až k určitému bodu
START TRANSACTION;

SELECT * FROM EMP;
UPDATE EMP SET AGE = AGE + 1;

SAVEPOINT samplesavepoint;

INSERT INTO EMP ('Mac', 'Mohan', 25, 'M', 2000);

ROLLBACK TO SAVEPOINT samplesavepoint;

COMMIT;

TCL SQL

  • Skupina příkazů, které se používají k řízení transakcí
  • Umožňují provádět operace jako zahájení, ukončení a správu transakcí

COMMIT​

  • Příkaz, který potvrzuje všechny změny provedené v rámci aktuální transakce a trvale je zapisuje do databáze

ROLLBACK​

  • Používá se ke zrušení všech změn provedených v aktuální transakci od posledního commitu

SAVEPOINT​

  • Vytvoření bodu uvnitř transakce, ke kterému můžeme později provést rollback
  • Transakce se vrátí k tomuto bodu, aniž by byly zrušeny všechny změny provedené po vytvoření savepointu

SET TRANSACTION​

  • Slouží k nastavení vlastností transakce, jako je izolace, úroveň izolace, zamykání atd

START TRANSACTION​

  • Slouží k zahájení nové transakce
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        START TRANSACTION
            ROLLBACK;
            RESIGNAL;
        END;
    
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

    IF(SELECT balance FROM accounts WHERE account_id = 1) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
COMMIT;